CREATE PROCEDURE [dbo].[eod_quotes_api]
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	-- 1. Step
	-- Make sure there are no duplicate entries in table (or merge will fail)
    WITH source AS (
		SELECT	  qot_sym_cd
				, qot_exc_cd
				, qot_trade_dt
				, qot_period
				, rn = ROW_NUMBER() OVER (PARTITION BY    qot_sym_cd
														, qot_exc_cd
														, qot_trade_dt
														, qot_period 
										  ORDER BY		qot_id DESC)
		FROM	raw.eod_quotes
	)
	DELETE FROM source WHERE rn > 1;

	-- 2. Step
	-- Merge data Quote into into final table
	MERGE INTO	fin.eod_quotes WITH (HOLDLOCK) AS target
	USING		(SELECT		  qot_trade_dt
							, qot_open
							, qot_high
							, qot_low
							, qot_close
							, qot_volume
							, qot_open_interest
							, exc_id
							, cat_id
							, sym_id
							, qot_adj_factor
				FROM		raw.eod_quotes q
				INNER JOIN	fin.eod_exchanges e 
							ON (q.qot_exc_cd = e.exc_cd)
				INNER JOIN	fin.com_categories c 
							ON (q.qot_period = c.cat_cd)
				INNER JOIN	fin.eod_symbols s 
							ON (q.qot_sym_cd = s.sym_cd 
								AND e.exc_id = s.sym_exc_id)) AS source
	ON				target.qot_sym_id = source.sym_id
				AND	target.qot_trade_dt = source.qot_trade_dt
				AND target.qot_cat_id = source.cat_id
	-- 2a: Existing Quote -> update data
	WHEN MATCHED THEN
		UPDATE SET	  target.qot_open = source.qot_open
					, target.qot_high = source.qot_high
					, target.qot_low = source.qot_low
					, target.qot_close = source.qot_close
					, target.qot_volume = source.qot_volume
					, target.qot_open_interest = source.qot_open_interest
					, target.qot_adj_factor = source.qot_adj_factor
	-- 2b: New Quote -> insert data
	WHEN NOT MATCHED BY TARGET THEN
		INSERT	( qot_sym_id
				, qot_trade_dt
				, qot_cat_id
				, qot_open
				, qot_high
				, qot_low
				, qot_close
				, qot_volume
				, qot_open_interest
				, qot_adj_factor )
		VALUES	( sym_id
				, qot_trade_dt
				, cat_id
				, qot_open
				, qot_high
				, qot_low
				, qot_close
				, qot_volume
				, qot_open_interest
				, qot_adj_factor );
END
